﻿using MongoDB.Bson;
using MongoDB.Driver;
using Newtonsoft.Json;
using Newtonsoft.Json.Serialization;
using SpreadsheetGear;
using Sustainalytics.Auth.Claims;
using Sustainalytics.BusinessResearchProductStates.Entities.ResearchState;
using Sustainalytics.Caching;
using Sustainalytics.ClientDW.DataAccessLayer;
using Sustainalytics.ClientDW.Entities;
using Sustainalytics.ClientDW.Entities.Universes;
using Sustainalytics.DataLayer;
using Sustainalytics.DataLayer.ConnectionStrings;
using Sustainalytics.DataLayer.Shared;
using Sustainalytics.Entities;
using Sustainalytics.Entities.ConnectionStrings;
using Sustainalytics.Entities.ESGRatings;
using Sustainalytics.Entities.ProductInvolvement;
using Sustainalytics.Entities.ScreeningTool;
using Sustainalytics.Entities.SustainableProducts;
using Sustainalytics.ESGRatings.BusinessLogic;
using Sustainalytics.ESGRatings.Computing.Entities;
using Sustainalytics.ESGRatings.DataContract.ESG;
using Sustainalytics.Portfolios.BL;
using Sustainalytics.ReportGenerator.BL;
using Sustainalytics.ScreeningTool.BL.Model;
using Sustainalytics.Utils;
using Sustainalytics.Utils.Configuration;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Diagnostics;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Net.Http;
using System.Net.Http.Formatting;
using System.Net.Http.Headers;
using System.Security.Claims;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using Factory = SpreadsheetGear.Factory;

namespace Sustainalytics.ScreeningTool.BL
{
    public class ExportScreening : IExportScreening
    {
        protected readonly IClientDwRepository _clientDWRepository;
        protected readonly ICompanyScreeningRepository _companyScreeningRepository;
        protected ICompanyScreening _companyScreening;
        protected readonly IUniversesRepository _universeRepository;
        protected readonly IPortfolioReadHandler _portfolioManager;
        protected ReportInformation ReportInformation;
        protected readonly IFilterCriteriaRepository _filterCriteriaRepository;
        protected readonly IRepositoryCollection<CorporateData> _repositoryCorporateData;
        protected readonly IScreeningDataRepository _screeningDataRepository;
        protected Dictionary<string, string> CompatibilityCharacters_CSV = new Dictionary<string, string>();
        private static readonly string CUSIP_NAME = "CUSIP";
        private static readonly string ISIN_NAME = "ISIN";

        public ExportScreening(
            ICompanyScreeningRepository companyScreeningRepository,
            IClientDwRepository clientDWRepository,
            IPortfolioReadHandler portfolioDetailsManager,
            IUniversesRepository universeRepository,
            IFilterCriteriaRepository filterCriteriaRepository,
            IRepositoryCollection<CorporateData> repositoryCorporateData,
            IScreeningDataRepository screeningDataRepository,
            IMongoCollection<CorporateData> corporateDataCollection,
            IMongoCollection<SustainableProductsFramework> sustainableProductsFramework,
            IMongoCollection<ProductInvolvementFramework> productInvolvementFramework
            )
        {
            _companyScreeningRepository = companyScreeningRepository;
            _clientDWRepository = clientDWRepository;
            _universeRepository = universeRepository;
            _filterCriteriaRepository = filterCriteriaRepository;
            _portfolioManager = portfolioDetailsManager;
            _repositoryCorporateData = repositoryCorporateData;
            _companyScreening = CompanyScreening.GetInstance(
                companyScreeningRepository,
                clientDWRepository,
                portfolioDetailsManager,
                universeRepository,
                filterCriteriaRepository,
                null,
                screeningDataRepository,
                corporateDataCollection,
                sustainableProductsFramework,
                productInvolvementFramework);
            _screeningDataRepository = screeningDataRepository;
            CompatibilityCharacters_CSV = new Dictionary<string, string>() { { "≥", ">=" }, { "≤", "<=" } };
        }

        protected Dictionary<Guid, List<Guid>> _dicCriteria;
        protected List<FilterCriteria> _criterias;
        protected string cnxClientDw = "";
        protected string cnxRedis = "";
        private Dictionary<Guid, List<int>> _criteriasToUniverses = new Dictionary<Guid, List<int>>();

        protected static Dictionary<string, GenerationProgress> ReportGenerationProgresses = new Dictionary<string, GenerationProgress>();
        public const int NUM_HEADER_COLUMNS_SCREENING_REPORT = 5;
        public const int NUM_HEADER_COLUMNS_REPORT = 4;
        public const int NUM_HEADER_COLUMNS_SCREENING_REPORT_CLIENT_ID = 5;
        public const int NUM_HEADER_COLUMNS_REPORT_CLIENT_ID = 4;

        protected ESGRatingsAdapter _adapterValues;

        protected ESGRatingsAdapter _adapterValues_TypeA_Public;
        protected ESGRatingsAdapter _adapterValues_TypeB_Public;

        protected ESGRatingsAdapter _adapterValues_TypeA_Private;
        protected ESGRatingsAdapter _adapterValues_TypeB_Private;

        protected ESGRatingsAdapter _adapterValues_TypeA_Combined;
        protected ESGRatingsAdapter _adapterValues_TypeB_Combined;

        protected ESGRatingsAdapter _adapterValues_Combined_Private;
        protected ESGRatingsAdapter _adapterValues_Combined_Public;

        private Dictionary<Guid, List<EsgFocusThresholds>> _esgThresholds;

        public byte[] RunScreeningExport(string exportDetails, ClaimsPrincipal claimsPrincipal)
        {
            Guid filterId = Guid.Parse(exportDetails);
            ReportInformation = new ReportInformation() { HasScreeningData = false };
            var filterDetails = _companyScreeningRepository.GetFilterByFilterId(filterId);
            var filterCriterias = _companyScreeningRepository.GetFilterCriteriasByFilterId(filterId);
            var defaultEsgProfile = _companyScreeningRepository.GetDefaultProfile();

            var accountId = claimsPrincipal.Claims.Where(s => s.Type == CustomClaimTypes.AccountId).Select(s => s.Value).SingleOrDefault();
            if (filterDetails.Type == FilterType.Standard)
            {
                try
                {
                    var pregen = new PregeneratedReportManager();
                    var output = pregen.GetReport(Guid.Parse(accountId), filterId);
                    if (output.Content != null) return output.Content;
                }
                catch
                {
                    //supress exception
                }
            }
            var accountUniverseIds = _universeRepository.GetUniversesByAccountId(Guid.Parse(accountId));
            var portfolioData = GetPortfolioDataFromFilter(filterDetails, accountUniverseIds);
            List<Guid> companiesById = null;
            if (filterDetails != null && filterDetails.PortfolioId != null)
            {
                companiesById = _portfolioManager.GetCompanyIdsByPortfolio(filterDetails.PortfolioId.Value).ToList();
            }

            ScreeningReportData screeningReportData = null;
            try
            {
                List<int> universeIds = claimsPrincipal.Claims.GetUniverseIds().ToList();

                screeningReportData = new ScreeningReportData();

                var accountName = claimsPrincipal.Claims.Where(s => s.Type == CustomClaimTypes.AccountName).Select(s => s.Value).SingleOrDefault();

                if (accountName != null)
                    screeningReportData.ClientName = accountName;

                screeningReportData.PortfolioName = filterDetails.PortfolioName;
                screeningReportData.FilterName = filterDetails.Name;
                screeningReportData.FilterSettings = filterDetails;

                var helper = new CUSIPISINClaimsCheckerBL();
                helper.checkIsinCusipClaims(ClaimsPrincipal.Current);

                screeningReportData.CompanyData = _screeningDataRepository.GetCompaniesData(filterDetails, universeIds, accountUniverseIds, portfolioData, filterCriterias, companiesById, helper.HasISINClaim, helper.HasCUSIPClaim);

                AddResultOutputToCompanyData(filterDetails, screeningReportData);

                GetCompanyDataFromComputation(filterDetails, filterCriterias, defaultEsgProfile);                    
                
                return WriteToXLS(screeningReportData, filterId, filterDetails, Guid.Parse(accountId), claimsPrincipal);
            }
            finally
            {
                GC.Collect();
                GC.WaitForPendingFinalizers();
                GC.Collect();
            }
        }

      
        public byte[] RunScreeningExportForFtp(string exportDetails)
        {
            Guid filterId = Guid.Parse(exportDetails);
            ReportInformation = new ReportInformation() { HasScreeningData = false };

            var filterDetails = _companyScreeningRepository.GetFilterByFilterId(filterId);
            var filterCriterias = _companyScreeningRepository.GetFilterCriteriasByFilterId(filterId);
            var defaultEsgProfile = _companyScreeningRepository.GetDefaultProfile();
            var accountId = filterDetails.AccountId;
            var accountName = _companyScreeningRepository.GetAccountNameForFtpReportByAccountId(filterDetails.AccountId);

            var claimsPrincipal = new ClaimsReaderForISINCUSIPClaims(ConfigurationManager.AppSettings["RedisServer"], Convert.ToInt32(ConfigurationManager.AppSettings["DistributionDataSet"]))
                   .CollectAccountClaimsForISINAndCUSIP(String.Format("{0}#@%{1}", accountName, accountId));

            var accountUniverses = _universeRepository.GetUniversesByAccountId(filterDetails.AccountId);

            var portfolioData = GetPortfolioDataFromFilter(filterDetails, accountUniverses);
            List<Guid> companiesById = null;

            if (filterDetails.PortfolioId != null)
            {
                companiesById = _portfolioManager.GetCompanyIdsByPortfolio(filterDetails.PortfolioId.Value).ToList();
            }

            var helper = new CUSIPISINClaimsCheckerBL();
            helper.checkIsinCusipClaims(claimsPrincipal);

            ScreeningReportData screeningReportData = new ScreeningReportData
            {
                CompanyData = _screeningDataRepository.GetCompaniesData(filterDetails, accountUniverses.Select(p => p.Id).ToList(), accountUniverses, portfolioData,
                        filterCriterias, companiesById, helper.HasISINClaim, helper.HasCUSIPClaim)
            };

            AddResultOutputToCompanyData(filterDetails, screeningReportData);

            GetCompanyDataFromComputation(filterDetails, filterCriterias, defaultEsgProfile);

            screeningReportData.ClientName = _companyScreeningRepository.GetAccountNameForFtpReportByAccountId(filterDetails.AccountId);

            screeningReportData.PortfolioName = filterDetails.PortfolioName;
            screeningReportData.FilterName = filterDetails.Name;

            return WriteToXLS(screeningReportData, filterId, filterDetails, filterDetails.AccountId, null); //add account id
        }

        protected void InitializeExportProcess()
        {
            var cnxClientPlatform = ConfigurationManager.ConnectionStrings["ClientPlatform"].ConnectionString;
            var connectionStringsManager = ConnectionStringsManager.GetInstance(
                ConnectionStringsRepository.GetInstance(cnxClientPlatform));
            cnxClientDw = connectionStringsManager.GetConnectionString(ConnectionStringType.ClientDW);
            _esgThresholds = new EsgThresholdManager<EsgFocusThresholds, string>(cnxClientDw).GetCompanyEsgThresholds();

            cnxRedis = ConfigurationManager.AppSettings["RedisServer"];
        }

        protected static string ExtractESGComputationUrl()
        {
            var localUrl = (string)ConfigurationManager.AppSettings["ESGComputeURL"];
            if (localUrl == null)
                localUrl = "http://localhost:10280/esgcomputingserv/api/v1/esgcompute";
            return localUrl;
        }

        protected static ESGRatingsDto ExecuteEsgComputationRequest(string url, ESGRatingsOptionsDto esgRatingsOptions)
        {
            using (var esgClient = new HttpClient())
            {
                esgClient.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
                var jsonMediaTypeFormatter = new JsonMediaTypeFormatter
                {
                    SerializerSettings = { ContractResolver = new CamelCasePropertyNamesContractResolver() }
                };
                var esgResponse = esgClient.PostAsync(url, esgRatingsOptions, jsonMediaTypeFormatter).Result;

                esgResponse.EnsureSuccessStatusCode();

                var res = esgResponse.Content.ReadAsAsync<ESGRatingsDto>(new[] { jsonMediaTypeFormatter }).Result;
                return res;
            }
        }

        protected List<PortfolioDetails> GetPortfolioDataFromFilter(Filter filterDetails, List<Universe> accountUniverseIds)
        {
            List<PortfolioDetails> portfolioData = null;

            var portfolioId = filterDetails.PortfolioId.GetValueOrDefault();
            var universes = accountUniverseIds.Select(a => a.Id).ToList();
            if (portfolioId != Guid.Empty && universes.Any())
            {
                var securities = _portfolioManager.GetPortfolioSecurities(portfolioId, universes);
                portfolioData = securities.Select(x => x.GetPortfolioDetails()).ToList();
            }

            return portfolioData;
        }

        protected void AddResultOutputToCompanyData(Filter filterDetails, ScreeningReportData screeningReportData)
        {
            if (filterDetails.TargetType == TargetType.Screening)
            {
                List<BsonDocument> result = new List<BsonDocument>();

                var fullResultOutput = _companyScreening.RunSelectedCriteriaOnScreeningData(filterDetails.Id,
                    filterDetails.PortfolioId ?? Guid.Empty, false, true, filterDetails.AccountId, screeningReportData);

                foreach (var companyData in screeningReportData.CompanyData)
                {
                    var resultOutput = fullResultOutput.First(x => x["CompanyId"].AsGuid == companyData["_id"].AsGuid);
                    var item = new BsonDocument(companyData);
                    item.Add("IsValid", resultOutput["IsValid"]);
                    item.Add("Criteria", resultOutput["Criteria"]);
                    result.Add(item);
                }

                screeningReportData.CompanyData = result;
            }
        }

        protected void GetCompanyDataFromComputation(
            Filter filterDetails, 
            List<FilterCriteria> filterCriterias, 
            ESGProfile defaultEsgProfile, 
            Func<string> esgComputeUrlFunc = null, 
            string cnxClientDwEx = "")
        {
            if ((filterDetails.TargetType == TargetType.Report) &&
                (filterCriterias.Any(p =>
                    p.CriteriaType == CriteriaTypeEnum.CriteriaType5 ||
                    p.CriteriaType == CriteriaTypeEnum.CriteriaType6 ||
                    p.CriteriaType == CriteriaTypeEnum.CriteriaType8 ||
                    p.CriteriaType == CriteriaTypeEnum.CriteriaType10)))
            {
                string esgComputeUrl;
                if (esgComputeUrlFunc == null)
                {// screeningtool
                    // get data from computation
                    InitializeExportProcess();
                    esgComputeUrl = ExtractESGComputationUrl();
                }
                else
                {// pregen
                    _esgThresholds = new EsgThresholdManager<EsgFocusThresholds, string>(cnxClientDwEx).GetCompanyEsgThresholds();
                    esgComputeUrl = esgComputeUrlFunc();
                }

                var esgRatingsOptions = new ESGRatingsOptionsDto
                {
                    IncludeIndWeightedScores = true,
                    IncludeWeights = true,
                    IncludeAggregatedScores = true,
                    IncludeCompaniesInGroup = true,
                    MatrixId = defaultEsgProfile.MatrixId,
                    RefUnivId = defaultEsgProfile.ReferenceUniverseId,
                };

                if (filterDetails.ComparativeGroup != null)
                    esgRatingsOptions.GroupBy = filterDetails.ComparativeGroup.Value;

                if (filterDetails.WeightMatrixId != null)
                    esgRatingsOptions.MatrixId = filterDetails.WeightMatrixId.Value;

                if (filterDetails.ReferenceUniverseId.HasValue)
                    esgRatingsOptions.RefUnivId = filterDetails.ReferenceUniverseId.Value;

                if (filterDetails.CompanyType != null)
                {
                    var selCompanyType = (CompanyTypeEnum)filterDetails.CompanyType;
                    if (selCompanyType.HasFlag(CompanyTypeEnum.Public) || selCompanyType.HasFlag(CompanyTypeEnum.Private) || selCompanyType.HasFlag(CompanyTypeEnum.Combined))
                        esgRatingsOptions.CompanyType = CompanyTypeEnum.Combined;
                }

                if (filterDetails.ResearchType != null)
                    esgRatingsOptions.TemplateType = TemplateTypeEnum.Combined;

                if (filterDetails.WeightMatrixId != null)
                {
                    esgRatingsOptions.IncludeAggregatedWeights = true;
                    _adapterValues = new ESGRatingsAdapter(ExecuteEsgComputationRequest(esgComputeUrl, esgRatingsOptions));
                }

                if (IsFilterCompanyTypeCombined(filterDetails) && IsFilterResearchTypeCombined(filterDetails))
                {
                    _adapterValues =
                        new ESGRatingsAdapter(ExecuteEsgComputationRequest(esgComputeUrl, esgRatingsOptions));
                }

                if (filterDetails.CompanyTypePublicOrPrivate == 1 && filterDetails.ResearchType == 1)
                {
                    // TYPE A
                    esgRatingsOptions.TemplateType = TemplateTypeEnum.TypeA;
                    esgRatingsOptions.CompanyType = CompanyTypeEnum.Private;

                    _adapterValues_TypeA_Private =
                        new ESGRatingsAdapter(ExecuteEsgComputationRequest(esgComputeUrl, esgRatingsOptions));

                    esgRatingsOptions.TemplateType = TemplateTypeEnum.TypeA;
                    esgRatingsOptions.CompanyType = CompanyTypeEnum.Public;

                    _adapterValues_TypeA_Public =
                        new ESGRatingsAdapter(ExecuteEsgComputationRequest(esgComputeUrl, esgRatingsOptions));

                    // TYPE B
                    esgRatingsOptions.TemplateType = TemplateTypeEnum.TypeB;
                    esgRatingsOptions.CompanyType = CompanyTypeEnum.Private;

                    _adapterValues_TypeB_Private =
                        new ESGRatingsAdapter(ExecuteEsgComputationRequest(esgComputeUrl, esgRatingsOptions));

                    esgRatingsOptions.TemplateType = TemplateTypeEnum.TypeB;
                    esgRatingsOptions.CompanyType = CompanyTypeEnum.Public;

                    _adapterValues_TypeB_Public =
                        new ESGRatingsAdapter(ExecuteEsgComputationRequest(esgComputeUrl, esgRatingsOptions));
                }

                //
                if (IsFilterCompanyTypeCombined(filterDetails) &&
                    filterDetails.ResearchType == 1)
                {
                    esgRatingsOptions.TemplateType = TemplateTypeEnum.TypeA;
                    esgRatingsOptions.CompanyType = CompanyTypeEnum.Combined;

                    _adapterValues_TypeA_Combined =
                        new ESGRatingsAdapter(ExecuteEsgComputationRequest(esgComputeUrl, esgRatingsOptions));

                    esgRatingsOptions.TemplateType = TemplateTypeEnum.TypeB;
                    esgRatingsOptions.CompanyType = CompanyTypeEnum.Combined;

                    _adapterValues_TypeB_Combined =
                        new ESGRatingsAdapter(ExecuteEsgComputationRequest(esgComputeUrl, esgRatingsOptions));
                }

                //
                if (filterDetails.CompanyTypePublicOrPrivate == 1 &&
                    IsFilterResearchTypeCombined(filterDetails))
                {
                    esgRatingsOptions.TemplateType = TemplateTypeEnum.Combined;
                    esgRatingsOptions.CompanyType = CompanyTypeEnum.Private;

                    _adapterValues_Combined_Private =
                        new ESGRatingsAdapter(ExecuteEsgComputationRequest(esgComputeUrl, esgRatingsOptions));

                    esgRatingsOptions.TemplateType = TemplateTypeEnum.Combined;
                    esgRatingsOptions.CompanyType = CompanyTypeEnum.Public;

                    _adapterValues_Combined_Public =
                        new ESGRatingsAdapter(ExecuteEsgComputationRequest(esgComputeUrl, esgRatingsOptions));
                }
                // end get data from computation
            }
        }

        protected static bool IsFilterResearchTypeCombined(Filter filterDetails)
        {
            return (filterDetails.ResearchType == 2 || filterDetails.ResearchType == null);
        }

        protected static bool IsFilterCompanyTypeCombined(Filter filterDetails)
        {
            return (filterDetails.CompanyTypePublicOrPrivate == 2 || filterDetails.CompanyTypePublicOrPrivate == null);
        }

        public int GetProgressStatus()
        {
            var claimsPrincipal = ClaimsPrincipal.Current.Claims.Where(s => s.Type == CustomClaimTypes.UserId)
                .Select(x => x.Value)
                .SingleOrDefault();

            if (claimsPrincipal == null)
                throw new ArgumentNullException("Restricted");

            var updateValue = ReportGenerationProgresses.SingleOrDefault(c => c.Key == claimsPrincipal);

            if (updateValue.Value == null)
                return 0;

            return updateValue.Value.Progress;
        }

        public Tuple<int, Dictionary<Guid, List<int>>> GetNoAccesDataForPregeneration(
            Guid filterId, 
            Guid accountId, 
            Dictionary<Guid, List<int>> companies,
            bool hasISINClaim,
            bool hasCUSIPClaim)
        {
            Debug.WriteLine("Get no data pregeneration starting");
            var currentFilterCriterias = CurrentFilterCriterias(filterId, accountId).OrderBy(a => a.Order).ToList();
            Debug.WriteLine("Get current filter criteria done");
            var filterDetails = _companyScreeningRepository.GetFilterByFilterId(filterId);
            Debug.WriteLine("Beginning pregeneration no access");
            var output = GetNoAccesDataForPregeneration(
                companies, 
                filterDetails, 
                currentFilterCriterias,
                hasISINClaim,
                hasCUSIPClaim);

            var count = currentFilterCriterias.Count();
            currentFilterCriterias = null;
            filterDetails = null;
            return new Tuple<int, Dictionary<Guid, List<int>>>(count, output);
        }

        private Dictionary<Guid, List<int>> GetNoAccesDataForPregeneration(
            Dictionary<Guid, List<int>> companies, 
            Filter filterDetails,
            List<FilterCriteria> currentFilterCriterias,
            bool hasISINClaim,
            bool hasCUSIPClaim)
        {
            var processedCollumns = GetProcessedColumn(filterDetails);

            Debug.WriteLine("Creating access dictionary");
            var accesDictionary = CreateCheckForAccesDictionaryNonParallel(companies, currentFilterCriterias, hasISINClaim, hasCUSIPClaim);
            Debug.WriteLine("Creating access dictionary done");
            var valueToBeReturn = new Dictionary<Guid, List<int>>();

            foreach (var companyid in companies.Keys)
            {
                var dataCompany = accesDictionary.ContainsKey(companyid) ? accesDictionary[companyid] : new List<Guid>(0);
                var listColumn = new List<int>();
                foreach (var currentFilterCriteria in currentFilterCriterias)
                {
                    if (dataCompany.Contains(currentFilterCriteria.Id))
                        continue;
                    listColumn.Add(processedCollumns + currentFilterCriteria.Order);
                }
                valueToBeReturn.Add(companyid, listColumn);
            }

            accesDictionary = null;
            return valueToBeReturn;
        }

        protected byte[] WriteToXLS(ScreeningReportData screeningReportData, Guid filterId, Filter filterDetails, Guid accountId, ClaimsPrincipal claimsPrincipal)
        {
            using (IWorkbookSet workbook = Factory.GetWorkbookSet())
            {
                IWorkbook screeningToolExportWorkBook = null;                
                try
                {
                    var stopWatch = new Stopwatch();
                    stopWatch.Start();
                    Dictionary<string, TimeSpan> timespanResult = new Dictionary<string, TimeSpan>();

                    var currentFilterCriterias = CurrentFilterCriterias(filterId, accountId);

                    string templateFolder = ConfigurationManager.AppSettings["templateFolder"];
                    if (filterDetails.TargetType == TargetType.Screening)
                    {
                        screeningToolExportWorkBook = filterDetails.PortfolioId.HasValue
                            ? workbook.Workbooks.Open(templateFolder + @"\Sample Screening Report - ClientId.xlsx")
                            : workbook.Workbooks.Open(templateFolder + @"\Sample Screening Report.xlsx");
                    }
                    else
                    {
                        screeningToolExportWorkBook = filterDetails.PortfolioId.HasValue
                            ? workbook.Workbooks.Open(templateFolder + @"\Sample Report Report - ClientId.xlsx")
                            : workbook.Workbooks.Open(templateFolder + @"\Sample Report Report.xlsx");
                    }
                    var coverSheet = screeningToolExportWorkBook.Worksheets[0];
                    var resultSheet = screeningToolExportWorkBook.Worksheets[1];

                    bool isHeaderBuild = false;

                    Dictionary<Guid, CorporateData> coverageEntitiesWithParentsDictionary = new Dictionary<Guid, CorporateData>();
                    var allCoverageEntities = _repositoryCorporateData.ReadAllWhere(a => a.ResearchParentId != null, includeFields: new string[] { "Id", "ResearchParentId" });
                    var coverageEntityParentIds = allCoverageEntities.Select(parent => parent.ResearchParentId.Value);
                    var allResearchParentsWithCoverage = _repositoryCorporateData.ReadAllWhere(a => coverageEntityParentIds.Contains(a.Id), includeFields: new string[] { "Id", "TemplateType", "CompanyType" });

                    if (allCoverageEntities != null && allResearchParentsWithCoverage != null)
                    {
                        foreach (var coverageEntity in allCoverageEntities)
                        {
                            if (!coverageEntity.ResearchParentId.HasValue)
                                continue;

                            var parent = allResearchParentsWithCoverage.FirstOrDefault(c => c.Id == coverageEntity.ResearchParentId.Value);
                            coverageEntitiesWithParentsDictionary.Add(coverageEntity.Id, parent);
                        }
                    }

                    timespanResult.Add("number 1", stopWatch.Elapsed);
                    _dicCriteria = CreateCheckForAccesDictionary(screeningReportData.CompanyData, currentFilterCriterias);
                    _criterias = _companyScreeningRepository.GetFilterCriteriasByFilterId(filterId);
                    var rows = screeningReportData.CompanyData.Count;
                    var columns = _criterias.Count;

                    var spread = SettingsHelper.Read("MaxDegreeOfParallelism", 1);

                    var count = screeningReportData.CompanyData.Count;

                    int perSheet = count / spread;
                    var delta = count - perSheet * spread;
                    perSheet += delta;
                    var skip = 0;
                    var list = new Tuple<IWorksheet, IEnumerable<BsonDocument>>[spread];
                    var source = screeningReportData.CompanyData.OrderBy(p => p["CompanyNameLowerCase"].AsString);
                    for (int i = 0; i < spread; i++)
                    {
                        list[i] = new Tuple<IWorksheet, IEnumerable<BsonDocument>>(SpreadsheetGear.Factory.GetWorkbook().ActiveWorksheet, source.Skip(skip).Take(perSheet));
                        skip += perSheet;
                    }

                    var useRangeOptimizations = true;
                    var localPC = GetProcessedColumn(filterDetails);

                    timespanResult.Add("number 2", stopWatch.Elapsed);

                    Parallel.ForEach(list, tuple =>
                    {
                        if (tuple.Item2.IsNullOrEmpty())
                        {
                            return;
                        }
                        var headerIndex = 1;
                        var dict = new Dictionary<int, string>();
                        var subSheetRows = tuple.Item2.Count();

                        if (subSheetRows > 0)
                            tuple.Item1.Cells[1, localPC, subSheetRows, columns + localPC].NumberFormat = "@";
                        foreach (var output in tuple.Item2)
                        {
                            CorporateData parentEntityCorporateData = null;
                            if (coverageEntitiesWithParentsDictionary.ContainsKey(output["_id"].AsGuid))
                            {
                                parentEntityCorporateData = coverageEntitiesWithParentsDictionary[output["_id"].AsGuid];
                            }
                            WriteCompanyDataThread(filterId, filterDetails, tuple.Item1, headerIndex, output, ref isHeaderBuild,
                                dict, parentEntityCorporateData);
                            headerIndex++;
                        }
                        if (useRangeOptimizations)
                        {
                            SetupStyleForNumberData(tuple.Item1, tuple.Item2.Count(), dict);
                        }
                    });

                    timespanResult.Add("number 3", stopWatch.Elapsed);

                    var currentRow = 1;

                    foreach (var cs in list)
                    {
                        cs.Item1.UsedRange.Copy(resultSheet.Cells[currentRow, 0], PasteType.All, PasteOperation.None, true, false);
                        currentRow += cs.Item1.UsedRange.RowCount;
                    }
                    var headerIndexNew = 1;
                    var processedColumn = localPC;
                    foreach (FilterCriteria criteria in _criterias.OrderBy(p => p.Order))
                    {
                        string compatibilityModeCriteriaName = ParseCriteriaName(criteria.Name, filterDetails.ExportType);
                        resultSheet.Cells[headerIndexNew - 1, processedColumn].Value = compatibilityModeCriteriaName;
                        processedColumn++;
                    }
                    SetupCommonStyleData(resultSheet, rows, columns + localPC);

                    timespanResult.Add("number 4", stopWatch.Elapsed);
                    // Refresh AutoFilter for the newly formed headers.
                    resultSheet.Cells.AutoFilter();
                    resultSheet.Cells.AutoFilter();

                    WriteCoverSheet(screeningReportData, coverSheet);
                    timespanResult.Add("number 5", stopWatch.Elapsed);

                    if (filterDetails.TargetType == TargetType.Screening)
                    {
                        var filterSettingsSheet = screeningToolExportWorkBook.Worksheets[2];
                        WriteFiltersSheet(filterSettingsSheet);
                    }

                    timespanResult.Add("number 6", stopWatch.Elapsed);

                    if (filterDetails.ExportType != null)
                    {
                        if (filterDetails.ExportType.Value == ExportType.Excel)
                            return screeningToolExportWorkBook.SaveToMemory(FileFormat.OpenXMLWorkbook);
                        else
                        {
                            screeningToolExportWorkBook.Worksheets[1].Name = "ScreeningResults";
                            return screeningToolExportWorkBook.Worksheets[1].SaveToMemory(FileFormat.CSV);
                        }
                    }

                    timespanResult.Add("number 7", stopWatch.Elapsed);
                    return null;
                }
                finally
                {
                    if (screeningToolExportWorkBook != null)
                        screeningToolExportWorkBook.Close();
                }
            }
        }

        private string ParseCriteriaName(string name, ExportType? exportType)
        {
            string resultString = name;
            if (exportType == ExportType.CSV)
            {
                foreach (KeyValuePair<string, string> entry in CompatibilityCharacters_CSV)
                {
                    resultString = resultString.Replace(entry.Key, entry.Value);
                }
            }
            return resultString;
        }

        private List<FilterCriteria> CurrentFilterCriterias(Guid filterId, Guid accountId)
        {
            var currentFilterCriterias = _companyScreeningRepository.GetFilterCriteriasByFilterId(filterId);

            PrepareCriteriaToUniverses(accountId, currentFilterCriterias);
            return currentFilterCriterias;
        }

        private void PrepareCriteriaToUniverses(Guid accountId, List<FilterCriteria> currentFilterCriterias)
        {
            Dictionary<Guid, int> criteriasToFeatures =
                _companyScreeningRepository.ListFeatureUniversesByCriteriaLevelTwoId(
                    currentFilterCriterias.Select(p => p.CriteriaLevel2Id).ToList())
                    .ToDictionary(c => c.Id, c => c.UniverseFeature);

            if (currentFilterCriterias.Any())
                _criteriasToUniverses = criteriasToFeatures.ToDictionary(criterias => criterias.Key,
                    criterias => _universeRepository.GetUniverseIdsByFeatureIds(criterias.Value, accountId));
            else
            {
                _criteriasToUniverses = new Dictionary<Guid, List<int>>();
            }
        }

        protected void SetupStyleForNumberData(IWorksheet resultWorksheet, int rows, Dictionary<int, string> columnsWithDefaultNumberFormat)
        {
            foreach (var data in columnsWithDefaultNumberFormat)
            {
                Console.WriteLine(string.Format("Key:{0}, Rows:{1}, Value:{2}", data.Key, rows, data.Value));
                if (rows > 0)
                    resultWorksheet.Cells[1, data.Key, rows, data.Key].NumberFormat = data.Value;
            }
        }

        protected static void SetupCommonStyleData(IWorksheet resultSheet, int rows, int columns)
        {
            columns = columns - 1;
            resultSheet.Cells[0, 0, rows, columns].Font.Size = 11;
            resultSheet.Cells[0, 0, rows, columns].Font.Name = "Calibri";
            resultSheet.Cells[0, 0, rows, columns].WrapText = true;
            resultSheet.Cells[0, 0, rows, columns].ColumnWidth = 20.14;

            //setup header data

            SetupHeaderRange(resultSheet, columns);
            //company row
            if (rows > 0)
            {
                resultSheet.Cells[1, 0, rows, 0].Font.Bold = true;
                resultSheet.Cells[1, 0, rows, 0].HorizontalAlignment = HAlign.Left;
                resultSheet.Cells[1, 0, rows, 0].Font.Underline = UnderlineStyle.Single;
                resultSheet.Cells[1, 0, rows, 0].Font.Color = Color.FromArgb(44, 175, 164);
                resultSheet.Cells[1, 3, rows, 3].HorizontalAlignment = HAlign.Left;
            }
        }

        protected static void SetupHeaderRange(IWorksheet resultSheet, int columns)
        {
            resultSheet.Cells[0, 0, 0, columns].VerticalAlignment = VAlign.Top;
            resultSheet.Cells[0, 0, 0, columns].HorizontalAlignment = HAlign.Left;
            resultSheet.Cells[0, 0, 0, columns].Cells.Interior.Color = Color.FromArgb(44, 175, 164);
            resultSheet.Cells[0, 0, 0, columns].Font.Bold = true;
            resultSheet.Cells[0, 0, 0, columns].Font.Color = Color.FromArgb(255, 255, 255);
        }

        protected static int GetProcessedColumn(Filter filterDetails)
        {
            var processedColumn = filterDetails.TargetType == TargetType.Screening
                ? NUM_HEADER_COLUMNS_SCREENING_REPORT
                : NUM_HEADER_COLUMNS_REPORT;
            if (filterDetails.PortfolioId.HasValue)
            {
                processedColumn = filterDetails.TargetType == TargetType.Screening
                    ? NUM_HEADER_COLUMNS_SCREENING_REPORT_CLIENT_ID
                    : NUM_HEADER_COLUMNS_REPORT_CLIENT_ID;
            }
            return processedColumn;
        }

        protected void WriteFixedColumns(IWorksheet resultSheet,
            int headerIndex, BsonDocument companyData, Filter filterDetails)
        {
            BsonElement value;
            var columnIndex = 0;

            resultSheet.Cells[headerIndex, columnIndex].Value = companyData["CompanyName"].AsString;

            resultSheet.Hyperlinks.Add(
                resultSheet.Cells[headerIndex, 0],
                ConfigurationManager.AppSettings["platformUrl"] + "#/research/company/" + companyData["CompanyNameUrlFriendly"].AsString + "/overview", 
                null, 
                null, 
                companyData["CompanyName"].AsString);

            Interlocked.Increment(ref columnIndex);

            resultSheet.Cells[headerIndex, columnIndex].Value = companyData.TryGetElement("CapitalIqId", out value)
                ? companyData["CapitalIqId"].AsInt32.ToString()
                : "N/A";

            Interlocked.Increment(ref columnIndex);

            if (filterDetails.PortfolioId.HasValue)
            {
                ProcessCellFromBson(resultSheet, headerIndex, companyData, columnIndex, "SecurityName");
                Interlocked.Increment(ref columnIndex);
                ProcessCellFromBson(resultSheet, headerIndex, companyData, columnIndex, "SecurityID");
                Interlocked.Increment(ref columnIndex);
            }
            else
            {
                ProcessCellFromBson(resultSheet, headerIndex, companyData, columnIndex, "MainExchange");
                Interlocked.Increment(ref columnIndex);
                ProcessCellFromBson(resultSheet, headerIndex, companyData, columnIndex, "MainTicker");
                Interlocked.Increment(ref columnIndex);
            }

            // Only from Screening Tool
            if (filterDetails.TargetType == TargetType.Screening)
            {
                resultSheet.Cells[headerIndex, columnIndex].Font.Color = (ResultIsValid)companyData["IsValid"].AsInt32 ==
                    ResultIsValid.NotAvailable
                        ? Color.FromArgb(0, 0, 0)
                    : (ResultIsValid)companyData["IsValid"].AsInt32 == ResultIsValid.Valid
                            ? Color.FromArgb(0, 128, 0)
                            : Color.FromArgb(255, 0, 0);

                resultSheet.Cells[headerIndex, columnIndex].Value = (ResultIsValid)companyData["IsValid"].AsInt32 ==
                                                                    ResultIsValid.Valid
                    ? "Yes"
                    : (ResultIsValid)companyData["IsValid"].AsInt32 == ResultIsValid.NotValid
                        ? "No"
                        : "N/A";
            }
        }

        protected static void ProcessCellFromBson(IWorksheet resultSheet, int headerIndex, BsonDocument companyData,
            int columnIndex, string stringColName)
        {
            BsonElement value;

            if (companyData.TryGetElement(stringColName, out value))
            {
                resultSheet.Cells[headerIndex, columnIndex].NumberFormat = "@";
                resultSheet.Cells[headerIndex, columnIndex].Value =
                    companyData[stringColName] != BsonNull.Value
                        ? companyData[stringColName].ToString() : "No data";
            }
            else
            {
                resultSheet.Cells[headerIndex, columnIndex].Value = "No data";
            }
        }

        protected void WriteCompanyDataThread(Guid filterId, Filter filterDetails, IWorksheet resultSheet,
            int headerIndex,
            BsonDocument output, ref bool isHeaderBuild, Dictionary<int, string> dict, CorporateData researchEntity = null,
            bool useRangeOptimizations = true)
        {
            var processedColumn = GetProcessedColumn(filterDetails);
            // Write fixed columns according to the Bussiness Req.
            WriteFixedColumns(resultSheet, headerIndex, output, filterDetails);
            // Write Results
            WriteCriteriaResults(resultSheet, headerIndex, processedColumn, output, filterDetails, dict,
                researchEntity);
        }

        protected static Color GetFontColor(CriteriaResultOutput criteriaValidity)
        {
            if (criteriaValidity != null && criteriaValidity.IsValid == ResultIsValid.NotAvailable)
                return Color.FromArgb(0, 0, 0);

            if (criteriaValidity != null && criteriaValidity.IsValid == ResultIsValid.Valid)
                return Color.FromArgb(0, 0, 0);

            return Color.FromArgb(255, 0, 0);
        }

        protected bool CheckForAccess(BsonDocument companyData, FilterCriteria criteriaOutput)
        {
            var currentCompanyUniverses = companyData["UniverseIds"].AsBsonArray.ToList().Select(p => p.AsInt32);

            return currentCompanyUniverses.Intersect(_criteriasToUniverses[criteriaOutput.CriteriaLevel2Id]).Any();
        }

        protected static string GetCompanyDataCriteria(BsonDocument companyData, FilterCriteria criteriaOutput)
        {
            BsonElement value;
            if (!companyData.TryGetElement(criteriaOutput.Name.Replace(".", ""), out value))
                return "No data";

            var bsonValue = companyData[criteriaOutput.Name.Replace(".", "")];

            if (bsonValue.IsInt32)
                return bsonValue.AsInt32.ToString();

            if (bsonValue.IsDouble)
                return Math.Round(bsonValue.AsDouble, 2, MidpointRounding.AwayFromZero).ToString("0.00");

            return bsonValue.AsString;
        }

        protected static void MapIndicatorDataToEsgDataAdapter(IWorksheet resultSheet, int headerIndex, int processedColumn,
            FilterCriteria criteriaOutput, ESGRatingsAdapter.IndicatorDataStruct indicator, BsonDocument companyData, ESGRatingsAdapter.ESGData scores)
        {
            if (scores.Score >= 0)
            {
                if (criteriaOutput.Name.Contains("-Weight"))
                    resultSheet.Cells[headerIndex, processedColumn].Value = indicator.Weight.ToString("0.0000");

                if (criteriaOutput.Name.Contains("-Weighted Score"))
                    resultSheet.Cells[headerIndex, processedColumn].Value = indicator.WeightedScore.ToString("0.0000");

                if (criteriaOutput.Name.Contains("-Raw Score"))
                    resultSheet.Cells[headerIndex, processedColumn].Value = indicator.Score;

                if (criteriaOutput.Name.Contains("-Answer Category"))
                {
                    resultSheet.Cells[headerIndex, processedColumn].Value =
                        CriteriaValueFormatter.FormatCriteriaValue(companyData, criteriaOutput);
                }

                if (criteriaOutput.Name.Contains("-Assessment") || criteriaOutput.Name.Contains("-Outlook"))
                {
                    var data = GetDataFromBson(criteriaOutput, companyData);
                    resultSheet.Cells[headerIndex, processedColumn].Value = data;
                }
            }
            else
            {
                resultSheet.Cells[headerIndex, processedColumn].Value = "No data";
            }
        }

        protected static void MapFocusedRatingsIndicatorData(IWorksheet resultSheet, int headerIndex,
            int processedColumn, FilterCriteria criteriaOutput, BsonDocument companyData)
        {
            resultSheet.Cells[headerIndex, processedColumn].Value = "No data";

            if (criteriaOutput.Name.Contains("-Answer Category"))
            {
                resultSheet.Cells[headerIndex, processedColumn].Value =
                    CriteriaValueFormatter.FormatCriteriaValue(companyData, criteriaOutput);
            }

            if (criteriaOutput.Name.Contains("-Raw Score"))
            {
                resultSheet.Cells[headerIndex, processedColumn].Value =
                    GetDataFromBson(criteriaOutput, companyData);
            }
        }

        protected int WriteCriteriaResults(IWorksheet resultSheet, int headerIndex, int processedColumn,
            BsonDocument companyData, Filter filterDetails, Dictionary<int, string> columnsDefault,
            CorporateData parentEntityCorporateData = null, bool useRangeOptimizations = true)
        {
            BsonElement value;
            var indicators = new Dictionary<string, ESGRatingsAdapter.IndicatorDataStruct>();
            var scores = new ESGRatingsAdapter.ESGData();
            var themeScores = new ESGRatingsAdapter.ESGAvg();
            var themesPerformance = new List<ThemePerformance>();
            OverallPerformance performance = null;
            var companyIdUsed = companyData["_id"].AsGuid;
            var templateType = string.Empty;
            var companyType = string.Empty;

            if (parentEntityCorporateData == null)
            {
                if (companyData.TryGetElement("TemplateType", out value))
                    templateType = companyData["TemplateType"].AsString;

                if (companyData.TryGetElement("Company type", out value))
                    companyType = companyData["Company type"].AsString;
            }
            else
            {
                companyIdUsed = parentEntityCorporateData.Id;
                templateType = parentEntityCorporateData.TemplateType;
                companyType = parentEntityCorporateData.CompanyType;
            }

            Action<ESGRatingsAdapter> GetDataFromAdapter = adapter =>
            {
                if (adapter != null)
                {
                    indicators = adapter.GetCompanyIndicatorData(companyIdUsed);
                    scores = adapter.GetEsgData(companyIdUsed);
                    themeScores = adapter.GetESGAvg(companyIdUsed);

                    if (companyData.HasComprehensiveEsgResearchAvailable())
                    {
                        adapter.LoadEsgFocus(_esgThresholds);
                        performance = adapter.GetCompanyPerformance(companyIdUsed);

                        if (filterDetails.WeightMatrixId.HasValue && filterDetails.WeightMatrixId.Value != Guid.Empty)
                            themesPerformance = adapter.GetThemesPerformance(companyIdUsed);
                    }
                }
            };

            //CASE 1 : [Combine Company Types]=YES || [Combine ResearchTypes]=YES
            if (IsFilterResearchTypeCombined(filterDetails) || IsFilterCompanyTypeCombined(filterDetails))
            {
                GetDataFromAdapter.Invoke(_adapterValues);
            }

            if (!string.IsNullOrWhiteSpace(templateType))
            {
                //CASE 2 : [Combine Company Types]=NO && [Combine ResearchTypes]=NO
                if (filterDetails.CompanyTypePublicOrPrivate == 1 && filterDetails.ResearchType == 1)
                {
                    if (templateType == "Type A" && companyType == "Public")
                        GetDataFromAdapter.Invoke(_adapterValues_TypeA_Public);

                    if (templateType == "Type B" && companyType == "Public")
                        GetDataFromAdapter.Invoke(_adapterValues_TypeB_Public);

                    if (templateType == "Type A" && companyType == "Private")
                        GetDataFromAdapter.Invoke(_adapterValues_TypeA_Private);

                    if (templateType == "Type B" && companyType == "Private")
                        GetDataFromAdapter.Invoke(_adapterValues_TypeB_Private);
                }

                //CASE 3 : [Combine Company Types]=YES && [Combine ResearchTypes]=NO
                if (IsFilterCompanyTypeCombined(filterDetails) && filterDetails.ResearchType == 1)
                {
                    if (templateType == "Type A")
                        GetDataFromAdapter.Invoke(_adapterValues_TypeA_Combined);

                    if (templateType == "Type B")
                        GetDataFromAdapter.Invoke(_adapterValues_TypeB_Combined);
                }

                //CASE 4 : [Combine Company Types]=NO  && [Combine ResearchTypes]=YES
                if (filterDetails.CompanyTypePublicOrPrivate == 1 && IsFilterResearchTypeCombined(filterDetails))
                {
                    if (companyType == "Public")
                        GetDataFromAdapter.Invoke(_adapterValues_Combined_Public);

                    if (companyType == "Private")
                        GetDataFromAdapter.Invoke(_adapterValues_Combined_Private);
                }
            }

            Stopwatch stopWatch = new Stopwatch();
            stopWatch.Start();

            Dictionary<string, CriteriaResultOutput> resultOutputCriterias =
                new Dictionary<string, CriteriaResultOutput>();

            if (filterDetails.TargetType == TargetType.Screening)
            {
                resultOutputCriterias =
                    JsonConvert.DeserializeObject<List<CriteriaResultOutput>>(
                        companyData["Criteria"].AsString)
                        .Distinct()
                        .ToDictionary(p => p.FilterCriteriaId.ToString(), d => d);
            }
            var companyId = companyData["_id"].AsGuid;

            var dataCompany = _dicCriteria.ContainsKey(companyId) ? _dicCriteria[companyId] : new List<Guid>();
            foreach (var criteriaOutput in _criterias.OrderBy(p => p.Order))
            {
                if (dataCompany.Contains(criteriaOutput.Id))
                {
                    CriteriaResultOutput criteriaValidity;
                    resultOutputCriterias.TryGetValue(criteriaOutput.Id.ToString(), out criteriaValidity);

                    switch (criteriaOutput.CriteriaType)
                    {
                        case CriteriaTypeEnum.CriteriaType5:

                            if (!useRangeOptimizations) resultSheet.Cells[headerIndex, processedColumn].NumberFormat = "@";

                            if (criteriaValidity != null && criteriaValidity.StringValue == "No Access")
                            {
                                resultSheet.Cells[headerIndex, processedColumn].Value = "No Access";
                            }
                            else
                            {
                                if (filterDetails.TargetType == TargetType.Report)
                                {
                                    if (criteriaOutput.ResearchProducts.ContainsAny(new List<ResearchProduct> { ResearchProduct.CoreRiskRating, ResearchProduct.RiskRating }))
                                    {
                                        resultSheet.Cells[headerIndex, processedColumn].Value =
                                            CriteriaValueFormatter.FormatCriteriaValue(companyData, criteriaOutput);

                                    }
                                    else if (indicators.Any() && companyData.HasComprehensiveEsgResearchAvailable())
                                    {
                                        if (indicators.ContainsKey(criteriaOutput.Name.Split(' ')[0].ToLowerInvariant()))
                                        {
                                            var indicator = indicators[criteriaOutput.Name.Split(' ')[0].ToLowerInvariant()];
                                            MapIndicatorDataToEsgDataAdapter(resultSheet, headerIndex, processedColumn,
                                                criteriaOutput, indicator, companyData, scores);
                                        }
                                        else
                                            resultSheet.Cells[headerIndex, processedColumn].Value = "No data";
                                    }
                                    else
                                    {
                                        if (companyData.HasComprehensiveEsgResearchAvailable())
                                            resultSheet.Cells[headerIndex, processedColumn].Value =
                                                CriteriaValueFormatter.FormatCriteriaValue(companyData, criteriaOutput);
                                        else if (companyData.HasCoreEsgResearchAvailable())
                                            MapFocusedRatingsIndicatorData(resultSheet, headerIndex, processedColumn, criteriaOutput, companyData);
                                        else
                                            resultSheet.Cells[headerIndex, processedColumn].Value = "No data";
                                    }
                                }

                                if (filterDetails.TargetType == TargetType.Screening)
                                {
                                    resultSheet.Cells[headerIndex, processedColumn].Font.Color = GetFontColor(criteriaValidity);
                                    resultSheet.Cells[headerIndex, processedColumn].Value =
                                        CriteriaValueFormatter.FormatCriteriaValue(companyData, criteriaOutput);
                                }

                                var companyIsInProgressOrNotApplicable = CompanyIsInProgressOrNotApplicable(companyData, criteriaOutput);
                                if (companyIsInProgressOrNotApplicable.Item1)
                                {
                                    resultSheet.Cells[headerIndex, processedColumn].Value = companyIsInProgressOrNotApplicable.Item2;
                                }
                            }

                            break;

                        case CriteriaTypeEnum.CriteriaType6:

                            if (!useRangeOptimizations) resultSheet.Cells[headerIndex, processedColumn].NumberFormat = "@";

                            if (criteriaValidity != null && criteriaValidity.StringValue == "No Access")
                            {
                                resultSheet.Cells[headerIndex, processedColumn].Value = "No Access";
                            }
                            else
                            {
                                if (criteriaOutput.ResearchProducts.ContainsAny(new List<ResearchProduct> { ResearchProduct.CoreRiskRating, ResearchProduct.RiskRating }))
                                {
                                    resultSheet.Cells[headerIndex, processedColumn].Value =
                                            CriteriaValueFormatter.FormatCriteriaValue(companyData, criteriaOutput);
                                }
                                else
                                {
                                    if (scores != null && filterDetails.TargetType == TargetType.Report && companyData.HasComprehensiveEsgResearchAvailable())
                                    {
                                        MapRankToEsgDataAdapter(resultSheet, headerIndex, processedColumn, criteriaOutput, scores, companyData);
                                        MapScoresToEsgDataAdapter(resultSheet, headerIndex, processedColumn, criteriaOutput, scores);
                                        MapPercentileToEsgDataAdapter(resultSheet, headerIndex, processedColumn, criteriaOutput, scores);
                                        MapCgaToEsgDataAdapter(resultSheet, headerIndex, processedColumn, criteriaOutput, themeScores, scores);
                                        MapPrepQuanQualDiscToEsgDataAdapter(resultSheet, headerIndex, processedColumn, criteriaOutput, scores);

                                        if (criteriaOutput.Name.ToLowerInvariant().Contains("Momentum".ToLowerInvariant()))
                                        {
                                            resultSheet.Cells[headerIndex, processedColumn].Value = CriteriaValueFormatter.FormatCriteriaValue(companyData, criteriaOutput);
                                        }

                                        if (criteriaOutput.Name.ToLowerInvariant().Contains("ESG Performance Assessment".ToLowerInvariant()))
                                        {
                                            if (performance != null && performance.CompanyAssessment != null)
                                                resultSheet.Cells[headerIndex, processedColumn].Value = performance.CompanyAssessment.Name;
                                            else
                                                resultSheet.Cells[headerIndex, processedColumn].Value = "No data";
                                        }

                                        if (criteriaOutput.Name.ToLowerInvariant().Contains("Research Type".ToLowerInvariant()))
                                            resultSheet.Cells[headerIndex, processedColumn].Value = "Comprehensive";
                                    }
                                    else if (companyData.HasCoreEsgResearchAvailable() && filterDetails.TargetType == TargetType.Report)
                                    {
                                        resultSheet.Cells[headerIndex, processedColumn].Value =
                                            CriteriaValueFormatter.FormatCriteriaValue(companyData, criteriaOutput);
                                    }
                                    else if (criteriaOutput.Name.Contains("SPL Code") && filterDetails.TargetType == TargetType.Report)
                                        resultSheet.Cells[headerIndex, processedColumn].Value =
                                            CriteriaValueFormatter.FormatCriteriaValue(companyData, criteriaOutput);
                                    else
                                        resultSheet.Cells[headerIndex, processedColumn].Value = "No data";
                                }


                                if (filterDetails.TargetType == TargetType.Screening)
                                {
                                    resultSheet.Cells[headerIndex, processedColumn].Font.Color = GetFontColor(criteriaValidity);

                                    resultSheet.Cells[headerIndex, processedColumn].Value =
                                        CriteriaValueFormatter.FormatCriteriaValue(companyData, criteriaOutput);
                                }

                                var companyIsInProgressOrNotApplicable = CompanyIsInProgressOrNotApplicable(companyData, criteriaOutput);
                                if (companyIsInProgressOrNotApplicable.Item1)
                                {
                                    resultSheet.Cells[headerIndex, processedColumn].Value = companyIsInProgressOrNotApplicable.Item2;
                                }
                            }
                            break;

                        case CriteriaTypeEnum.CriteriaType8:

                            if (!useRangeOptimizations) resultSheet.Cells[headerIndex, processedColumn].NumberFormat = "@";

                            if (criteriaValidity != null && criteriaValidity.StringValue == "No Access")
                            {
                                resultSheet.Cells[headerIndex, processedColumn].Value = "No Access";
                            }
                            else
                            {
                                if (indicators.Any() && filterDetails.TargetType == TargetType.Report &&
                                    (companyData.HasComprehensiveEsgResearchAvailable() || companyData.HasControversyResearchAvailable()))
                                {
                                    if (indicators.ContainsKey(criteriaOutput.Name.Split(' ')[0].ToLowerInvariant()))
                                    {
                                        var indicator = indicators[criteriaOutput.Name.Split(' ')[0].ToLowerInvariant()];

                                        MapIndicatorDataToEsgDataAdapter(resultSheet, headerIndex, processedColumn,
                                            criteriaOutput, indicator, companyData, scores);
                                    }
                                    else if (criteriaOutput.Name == "Highest Controversy Level-Answer Category")
                                    {
                                        resultSheet.Cells[headerIndex, processedColumn].Value =
                                            CriteriaValueFormatter.FormatCriteriaValue(companyData, criteriaOutput);
                                    }
                                    else
                                        resultSheet.Cells[headerIndex, processedColumn].Value = "No data";
                                }
                                else if (filterDetails.TargetType == TargetType.Report &&
                                    (companyData.HasComprehensiveEsgResearchAvailable() || companyData.HasControversyResearchAvailable()))
                                {
                                    resultSheet.Cells[headerIndex, processedColumn].Value =
                                        CriteriaValueFormatter.FormatCriteriaValue(companyData, criteriaOutput);
                                }
                                else
                                    resultSheet.Cells[headerIndex, processedColumn].Value = "No data";

                                if (filterDetails.TargetType == TargetType.Screening)
                                {
                                    resultSheet.Cells[headerIndex, processedColumn].Font.Color = GetFontColor(criteriaValidity);

                                    resultSheet.Cells[headerIndex, processedColumn].Value =
                                        CriteriaValueFormatter.FormatCriteriaValue(companyData, criteriaOutput);
                                }

                                var companyIsInProgressOrNotApplicable = CompanyIsInProgressOrNotApplicable(companyData, criteriaOutput);
                                if (companyIsInProgressOrNotApplicable.Item1)
                                {
                                    resultSheet.Cells[headerIndex, processedColumn].Value = companyIsInProgressOrNotApplicable.Item2;
                                }
                            }
                            break;

                        case CriteriaTypeEnum.CriteriaType10:
                            if (!useRangeOptimizations) resultSheet.Cells[headerIndex, processedColumn].NumberFormat = "@";

                            if (criteriaValidity != null && criteriaValidity.StringValue == "No Access")
                            {
                                resultSheet.Cells[headerIndex, processedColumn].Value = "No Access";
                            }
                            else
                            {
                                if (filterDetails.TargetType == TargetType.Report)
                                {
                                    if (themesPerformance.Any() && criteriaOutput.Name.Contains("Weight") && companyData.HasComprehensiveEsgResearchAvailable())
                                    {
                                        MapWeightDataToEsgAdapter(resultSheet, headerIndex, processedColumn, criteriaOutput,
                                            themesPerformance, companyData);
                                    }
                                    else
                                        resultSheet.Cells[headerIndex, processedColumn].Value =
                                            CriteriaValueFormatter.FormatCriteriaValue(companyData, criteriaOutput);
                                }

                                if (filterDetails.TargetType == TargetType.Screening)
                                {
                                    resultSheet.Cells[headerIndex, processedColumn].Font.Color =
                                        GetFontColor(criteriaValidity);
                                    resultSheet.Cells[headerIndex, processedColumn].Value =
                                        CriteriaValueFormatter.FormatCriteriaValue(companyData, criteriaOutput);
                                }

                                var companyIsInProgressOrNotApplicable = CompanyIsInProgressOrNotApplicable(companyData, criteriaOutput);
                                if (companyIsInProgressOrNotApplicable.Item1)
                                {
                                    resultSheet.Cells[headerIndex, processedColumn].Value = companyIsInProgressOrNotApplicable.Item2;
                                }
                            }
                            break;

                        default:

                            if (!useRangeOptimizations) resultSheet.Cells[headerIndex, processedColumn].NumberFormat = "@";

                            if (criteriaValidity != null && criteriaValidity.StringValue == "No Access")
                            {
                                resultSheet.Cells[headerIndex, processedColumn].Value = "No Access";
                            }
                            else
                            {
                                if (filterDetails.TargetType == TargetType.Screening)
                                {
                                    resultSheet.Cells[headerIndex, processedColumn].Font.Color = GetFontColor(criteriaValidity);
                                    resultSheet.Cells[headerIndex, processedColumn].Value = CriteriaValueFormatter.FormatCriteriaValue(companyData, criteriaOutput);
                                }
                                else
                                {
                                    resultSheet.Cells[headerIndex, processedColumn].Value =
                                        CriteriaValueFormatter.FormatCriteriaValue(companyData, criteriaOutput);
                                }
                                var companyIsInProgressOrNotApplicable = CompanyIsInProgressOrNotApplicable(companyData, criteriaOutput);
                                if (companyIsInProgressOrNotApplicable.Item1)
                                {
                                    resultSheet.Cells[headerIndex, processedColumn].Value = companyIsInProgressOrNotApplicable.Item2;
                                }
                            }
                            break;
                    }

                    if (criteriaOutput.CriteriaType == CriteriaTypeEnum.CriteriaType3)
                    {
                        if (!useRangeOptimizations)
                            resultSheet.Cells[headerIndex, processedColumn].NumberFormat = "@";

                        if (criteriaOutput.Name.StartsWith("Research Entity ID", StringComparison.InvariantCulture))
                        {
                            int capitalIqId;
                            if (int.TryParse(resultSheet.Cells[headerIndex, processedColumn].Value?.ToString(), out capitalIqId))
                            {
                                resultSheet.Cells[headerIndex, processedColumn].Value = capitalIqId;
                            }

                            resultSheet.Cells[headerIndex, processedColumn].NumberFormat = "General";
                        }
                    }
                    else
                    {
                        if (resultSheet.Cells[headerIndex, processedColumn].Value != null)
                        {
                            long intValue;
                            double doubleValue;

                            if (resultSheet.Cells[headerIndex, processedColumn].Value.ToString().EndsWith("%"))
                            {
                                var percentageValue =
                                    resultSheet.Cells[headerIndex, processedColumn].Value.ToString().TrimEnd('%');

                                double doublePercentageValue;
                                if (double.TryParse(percentageValue, out doublePercentageValue))
                                {
                                    resultSheet.Cells[headerIndex, processedColumn].Value = doublePercentageValue;
                                    if (columnsDefault.ContainsKey(processedColumn))
                                    {
                                        columnsDefault[processedColumn] = @"0.00\%";
                                    }
                                    else
                                    {
                                        columnsDefault.Add(processedColumn, @"0.00\%");
                                    }

                                    if (!useRangeOptimizations) resultSheet.Cells[headerIndex, processedColumn].NumberFormat = @"0.00\%";
                                }
                            }
                            else if (long.TryParse(resultSheet.Cells[headerIndex, processedColumn].Value.ToString(),
                                out intValue))
                            {
                                resultSheet.Cells[headerIndex, processedColumn].Value = intValue;
                                if (columnsDefault.ContainsKey(processedColumn))
                                {
                                    columnsDefault[processedColumn] = "0";
                                }
                                else
                                {
                                    columnsDefault.Add(processedColumn, "0");
                                }
                                if (!useRangeOptimizations) resultSheet.Cells[headerIndex, processedColumn].NumberFormat = "0";
                            }
                            else if (double.TryParse(resultSheet.Cells[headerIndex, processedColumn].Value.ToString(),
                                out doubleValue))
                            {
                                var lastString = resultSheet.Cells[headerIndex, processedColumn].Value.ToString().Split('.').LastOrDefault();
                                var numberOfDecimals = 0;
                                if (!string.IsNullOrWhiteSpace(lastString))
                                    numberOfDecimals = lastString.Length;

                                StringBuilder numberFormat = new StringBuilder("0");
                                if (numberOfDecimals > 0)
                                    numberFormat.Append(".");

                                for (int i = 0; i < numberOfDecimals; i++)
                                {
                                    numberFormat.Append("0");
                                }

                                resultSheet.Cells[headerIndex, processedColumn].Value = doubleValue;
                                if (columnsDefault.ContainsKey(processedColumn))
                                {
                                    columnsDefault[processedColumn] = numberFormat.ToString();
                                }
                                else
                                {
                                    columnsDefault.Add(processedColumn, numberFormat.ToString());
                                }

                                if (!useRangeOptimizations) resultSheet.Cells[headerIndex, processedColumn].NumberFormat = numberFormat.ToString();
                            }
                        }
                    }
                }
                else
                {
                    resultSheet.Cells[headerIndex, processedColumn].Value = "No Access";
                }

                Interlocked.Increment(ref processedColumn);
            }

            stopWatch.Restart();

            return processedColumn;
        }

        private Tuple<bool, string> CompanyIsInProgressOrNotApplicable(BsonDocument companyData, FilterCriteria filterCriteria)
        {
            if (filterCriteria.ResearchProducts == null)
                return new Tuple<bool, string>(false, null);

            var researchState = filterCriteria.ResearchProducts.GetResearchState(companyData);

            if (researchState == ResearchState.ResearchInProgress)
                return new Tuple<bool, string>(true, "Research in progress");

            if (researchState == ResearchState.FrameworkNotApplicable)
                return new Tuple<bool, string>(true, "Framework not applicable");

            return new Tuple<bool, string>(false, null);
        }

        protected static string GetDataFromBson(FilterCriteria criteriaOutput, BsonDocument companyData)
        {
            BsonElement value;
            var dataAnswer = "No data";
            if (companyData.TryGetElement(criteriaOutput.Name.Replace(".", ""), out value))
            {
                dataAnswer = value.Value.ToString();
                if (string.IsNullOrEmpty(dataAnswer))
                {
                    dataAnswer = "No data";
                }
            }
            return dataAnswer;
        }

        protected Dictionary<Guid, List<Guid>> CreateCheckForAccesDictionaryNonParallel(
            Dictionary<Guid, List<int>> companyUniverses, 
            List<FilterCriteria> criteriaData, 
            bool hasISINClaim, 
            bool hasCUSIPClaim)
        {
            var dict = new Dictionary<Guid, List<Guid>>();
            foreach (var companyUniverse in companyUniverses)
            {
                var list = new List<Guid>();
                foreach (var filterCriteria in criteriaData)
                {
                    if (!(companyUniverse.Value ?? new List<int>()).Intersect(_criteriasToUniverses[filterCriteria.CriteriaLevel2Id]).Any())
                        continue;

                    if (filterCriteria.Name == ISIN_NAME && !hasISINClaim)
                        continue;

                    if (filterCriteria.Name == CUSIP_NAME && !hasCUSIPClaim)
                        continue;

                    list.Add(filterCriteria.Id);
                }
                dict.Add(companyUniverse.Key, list);
            }
            return dict;
        }

        protected Dictionary<Guid, List<Guid>> CreateCheckForAccesDictionary(List<BsonDocument> companyData, List<FilterCriteria> criteriaData)
        {
            var dict = new Dictionary<Guid, List<Guid>>();
            Parallel.ForEach(companyData, a =>
            {
                var list = new List<Guid>();
                foreach (var filterCriteria in criteriaData)
                {
                    if (!CheckForAccess(a, filterCriteria))
                        continue;

                    list.Add(filterCriteria.Id);
                }
                lock (dict)
                {
                    if (!dict.ContainsKey(a["_id"].AsGuid))
                    {
                        dict.Add(a["_id"].AsGuid, list);
                    }
                }
            });
            return dict;
        }

        protected void MapPrepQuanQualDiscToEsgDataAdapter(IWorksheet resultSheet, int headerIndex, int processedColumn,
            FilterCriteria criteriaOutput, ESGRatingsAdapter.ESGData scores)
        {
            if (scores.Score >= 0)
            {
                if (criteriaOutput.Name.Contains("Preparedness"))
                    resultSheet.Cells[headerIndex, processedColumn].Value = Math.Round(scores.PreparednessScore,
                        MidpointRounding.AwayFromZero) >= 0
                        ? Math.Round(scores.PreparednessScore, MidpointRounding.AwayFromZero).ToString(CultureInfo.InvariantCulture)
                        : "No data";

                if (criteriaOutput.Name.Contains("Quantitative"))
                    resultSheet.Cells[headerIndex, processedColumn].Value = Math.Round(
                        scores.QuantitativePerformanceScore,
                        MidpointRounding.AwayFromZero) >= 0
                        ? Math.Round(scores.QuantitativePerformanceScore, MidpointRounding.AwayFromZero).ToString(CultureInfo.InvariantCulture)
                        : "No data";

                if (criteriaOutput.Name.Contains("Qualitative"))
                    resultSheet.Cells[headerIndex, processedColumn].Value = Math.Round(
                        scores.QualitativePerformanceScore,
                        MidpointRounding.AwayFromZero) >= 0
                        ? Math.Round(scores.QualitativePerformanceScore, MidpointRounding.AwayFromZero).ToString(CultureInfo.InvariantCulture)
                        : "No data";

                if (criteriaOutput.Name.Contains("Disclosure"))
                    resultSheet.Cells[headerIndex, processedColumn].Value = Math.Round(scores.DisclosureScore,
                        MidpointRounding.AwayFromZero) >= 0
                        ? Math.Round(scores.DisclosureScore, MidpointRounding.AwayFromZero).ToString(CultureInfo.InvariantCulture)
                        : "No data";
            }
            else
            {
                resultSheet.Cells[headerIndex, processedColumn].Value = "No data";
            }
        }

        protected void MapCgaToEsgDataAdapter(IWorksheet resultSheet, int headerIndex, int processedColumn,
            FilterCriteria criteriaOutput, ESGRatingsAdapter.ESGAvg themeScores, ESGRatingsAdapter.ESGData scores)
        {
            if (scores.Score >= 0)
            {
                if (criteriaOutput.Name.Contains("Comparative Group Average"))
                    resultSheet.Cells[headerIndex, processedColumn].Value = Math.Round(themeScores.OverallAvg,
                        MidpointRounding.AwayFromZero) >= 0
                        ? Math.Round(themeScores.OverallAvg, 2, MidpointRounding.AwayFromZero).ToString("0.00")
                        : "No data";

                if (criteriaOutput.Name.Contains("Comparative Group Average Environment"))
                    resultSheet.Cells[headerIndex, processedColumn].Value = Math.Round(themeScores.EAvg,
                        MidpointRounding.AwayFromZero) >= 0
                        ? Math.Round(themeScores.EAvg, 2, MidpointRounding.AwayFromZero).ToString("0.00")
                        : "No data";

                if (criteriaOutput.Name.Contains("Comparative Group Average Social"))
                    resultSheet.Cells[headerIndex, processedColumn].Value = Math.Round(themeScores.SAvg,
                        MidpointRounding.AwayFromZero) >= 0
                        ? Math.Round(themeScores.SAvg, 2, MidpointRounding.AwayFromZero).ToString("0.00")
                        : "No data";

                if (criteriaOutput.Name.Contains("Comparative Group Average Governance"))
                    resultSheet.Cells[headerIndex, processedColumn].Value = Math.Round(themeScores.GAvg,
                        MidpointRounding.AwayFromZero) >= 0
                        ? Math.Round(themeScores.GAvg, 2, MidpointRounding.AwayFromZero).ToString("0.00")
                        : "No data";
            }
            else
            {
                resultSheet.Cells[headerIndex, processedColumn].Value = "No data";
            }
        }

        protected void MapPercentileToEsgDataAdapter(IWorksheet resultSheet, int headerIndex, int processedColumn,
            FilterCriteria criteriaOutput, ESGRatingsAdapter.ESGData scores)
        {
            if (scores.Score >= 0)
            {
                if (criteriaOutput.Name.Contains("Percentile"))
                    resultSheet.Cells[headerIndex, processedColumn].Value = scores.Percentile >= 0
                        ? Math.Round((double)scores.Percentile, 2, MidpointRounding.AwayFromZero).ToString("0.00") + "%"
                        : "No data";

                if (criteriaOutput.Name.Contains("Environment Percentile"))
                    resultSheet.Cells[headerIndex, processedColumn].Value = scores.EnvironmentPercentile >= 0
                        ? Math.Round((double)scores.EnvironmentPercentile, 2, MidpointRounding.AwayFromZero).ToString("0.00") + "%"
                        : "No data";

                if (criteriaOutput.Name.Contains("Social Percentile"))
                    resultSheet.Cells[headerIndex, processedColumn].Value = scores.SocialPercentile >= 0
                        ? Math.Round((double)scores.SocialPercentile, 2, MidpointRounding.AwayFromZero).ToString("0.00") + "%"
                        : "No data";

                if (criteriaOutput.Name.Contains("Governance Percentile"))
                    resultSheet.Cells[headerIndex, processedColumn].Value = scores.GovernancePercentile >= 0
                        ? Math.Round((double)scores.GovernancePercentile, 2, MidpointRounding.AwayFromZero).ToString("0.00") + "%"
                        : "No data";
            }
            else
            {
                resultSheet.Cells[headerIndex, processedColumn].Value = "No data";
            }
        }

        protected void MapWeightDataToEsgAdapter(IWorksheet resultSheet, int headerIndex, int processedColumn,
            FilterCriteria criteriaOutput, List<ThemePerformance> themesPerformance, BsonDocument companyData)
        {
            string[] themeNames = { "Environment", "Social", "Governance" };

            if (themesPerformance.Count > 0)
            {
                foreach (var themeName in themeNames)
                {
                    if (criteriaOutput.Name.Contains(themeName))
                    {
                        resultSheet.Cells[headerIndex, processedColumn].Value =
                            themesPerformance.Any(t => t.ThemeName == themeName)
                                ? Math.Round((double)themesPerformance.First(t => t.ThemeName == themeName).Weight, 2,
                                    MidpointRounding.AwayFromZero).ToString("0.00")
                                : "No data";
                        break;
                    }
                }
            }
            else
            {
                resultSheet.Cells[headerIndex, processedColumn].Value = "No data";
            }
        }

        protected void MapScoresToEsgDataAdapter(IWorksheet resultSheet, int headerIndex, int processedColumn,
            FilterCriteria criteriaOutput, ESGRatingsAdapter.ESGData scores)
        {
            if (scores.Score >= 0)
            {
                if (criteriaOutput.Name.Contains("Score") && (!criteriaOutput.Name.Contains("Momentum")))
                    resultSheet.Cells[headerIndex, processedColumn].Value = Math.Round(scores.Score,
                        MidpointRounding.AwayFromZero) >= 0
                        ? Math.Round(scores.Score, 2, MidpointRounding.AwayFromZero).ToString("0.00")
                        : "No data";

                if (criteriaOutput.Name.Contains("Environment Score") && (!criteriaOutput.Name.Contains("Momentum")))
                    resultSheet.Cells[headerIndex, processedColumn].Value = Math.Round(scores.EnvironmentScore,
                        MidpointRounding.AwayFromZero) >= 0
                        ? Math.Round(scores.EnvironmentScore, 2, MidpointRounding.AwayFromZero).ToString("0.00")
                        : "No data";

                if (criteriaOutput.Name.Contains("Social Score") && (!criteriaOutput.Name.Contains("Momentum")))
                    resultSheet.Cells[headerIndex, processedColumn].Value = Math.Round(scores.SocialScore,
                        MidpointRounding.AwayFromZero) >= 0
                        ? Math.Round(scores.SocialScore, 2, MidpointRounding.AwayFromZero).ToString("0.00")
                        : "No data";

                if (criteriaOutput.Name.Contains("Governance Score") && (!criteriaOutput.Name.Contains("Momentum")))
                    resultSheet.Cells[headerIndex, processedColumn].Value = Math.Round(scores.GovernanceScore,
                        MidpointRounding.AwayFromZero) >= 0
                        ? Math.Round(scores.GovernanceScore, 2, MidpointRounding.AwayFromZero).ToString("0.00")
                        : "No data";
            }
            else
            {
                resultSheet.Cells[headerIndex, processedColumn].Value = "No data";
            }
        }

        private void MapRankToEsgDataAdapter(IWorksheet resultSheet, int headerIndex, int processedColumn,
            FilterCriteria criteriaOutput, ESGRatingsAdapter.ESGData scores, BsonDocument companyData)
        {
            if (scores.Score >= 0)
            {
                if (criteriaOutput.Name.Contains("Rank"))
                    resultSheet.Cells[headerIndex, processedColumn].Value = scores.Rank >= 0
                        ? scores.Rank + " out of " + scores.GroupCount
                        : "No data";

                if (criteriaOutput.Name.Contains("Environment Rank"))
                    resultSheet.Cells[headerIndex, processedColumn].Value = scores.EnvironmentRank >= 0
                        ? scores.EnvironmentRank + " out of " +
                          scores.GroupCount
                        : "No data";

                if (criteriaOutput.Name.Contains("Social Rank"))
                    resultSheet.Cells[headerIndex, processedColumn].Value = scores.SocialRank >= 0
                        ? scores.SocialRank + " out of " +
                          scores.GroupCount
                        : "No data";

                if (criteriaOutput.Name.Contains("Governance Rank"))
                    resultSheet.Cells[headerIndex, processedColumn].Value = scores.GovernanceRank >= 0
                        ? scores.GovernanceRank + " out of " +
                          scores.GroupCount
                        : "No data";

                if (criteriaOutput.Name.Contains("SPL Code"))
                    resultSheet.Cells[headerIndex, processedColumn].Value =
                        companyData[criteriaOutput.Name.Replace(".", "")].ToString();
            }
            else
            {
                resultSheet.Cells[headerIndex, processedColumn].Value = "No data";
            }
        }

        protected void WriteCoverSheet(ScreeningReportData screeningReportData, IWorksheet coverSheet)
        {
            coverSheet.Cells["D3"].Value = DateTime.Now.ToString("dd/MM/yyyy");

            if (screeningReportData.PortfolioName != null)
                coverSheet.Cells["D4"].Value = screeningReportData.PortfolioName;

            coverSheet.Cells["D5"].Value = screeningReportData.FilterName;
            coverSheet.Cells["D6"].Value = screeningReportData.ClientName;
        }

        protected void WriteFiltersSheet(IWorksheet filterSettingsSheet)
        {
            var headerIndex = 1;
            var processedColumn = 0;

            // get all filtercriterias for the current filter
            var filterCriteria = _criterias;

            if (filterCriteria != null)
            {
                foreach (var filterCriterion in filterCriteria)
                {
                    filterSettingsSheet.Cells[headerIndex, processedColumn].Borders.Color = Color.FromArgb(128, 128, 128);
                    filterSettingsSheet.Cells[headerIndex, processedColumn].Borders.Weight = BorderWeight.Thin;
                    filterSettingsSheet.Cells[headerIndex, processedColumn].Borders.LineStyle = LineStyle.Continous;
                    filterSettingsSheet.Cells[headerIndex, processedColumn].Font.Bold = true;
                    filterSettingsSheet.Cells[headerIndex, processedColumn].Font.Name = "Calibri";
                    filterSettingsSheet.Cells[headerIndex, processedColumn].Font.Size = 11;

                    filterSettingsSheet.Cells[headerIndex, processedColumn + 1].Borders.Color = Color.FromArgb(128, 128, 128);
                    filterSettingsSheet.Cells[headerIndex, processedColumn + 1].Borders.Weight = BorderWeight.Thin;
                    filterSettingsSheet.Cells[headerIndex, processedColumn + 1].Borders.LineStyle = LineStyle.Continous;
                    filterSettingsSheet.Cells[headerIndex, processedColumn + 1].Font.Name = "Calibri";
                    filterSettingsSheet.Cells[headerIndex, processedColumn + 1].Font.Size = 11;

                    filterSettingsSheet.Cells[headerIndex, processedColumn].Value = filterCriterion.Name;

                    if (filterCriterion.SelectedValueInformation != null)
                    {
                        filterSettingsSheet.Cells[headerIndex, processedColumn + 1].NumberFormat = "@";
                        filterSettingsSheet.Cells[headerIndex, processedColumn + 1].Value =
                            filterCriterion
                                .SelectedValueInformation;
                    }
                    else
                        filterSettingsSheet.Cells[headerIndex, processedColumn + 1].Value = "";

                    headerIndex++;
                }
            }
        }
    }

    public class ClaimsReaderForISINCUSIPClaims
    {
        private static IAppRedisCache _redisCache;
        public ClaimsReaderForISINCUSIPClaims(string redisAddress, int databaseId)
        {
            if (_redisCache == null)
                _redisCache = new AppRedisCache(redisAddress, databaseId);

        }
        public ClaimsPrincipal CollectAccountClaimsForISINAndCUSIP(string accountName)
        {
            string ISINClaim = CUSIPISINClaimsCheckerBL.CLAIMTYPE_ISIN;
            string CUSIPClaim = CUSIPISINClaimsCheckerBL.CLAIMTYPE_CUSIP;
            string expected = CUSIPISINClaimsCheckerBL.CLAIMVALUE;


            var claimsForAccount = _redisCache.GetSet(accountName);
            if (claimsForAccount == null)
                throw new ApplicationException("claimsForAccount id null for account" + accountName);

            var claims = new List<Claim>();
            if (claimsForAccount.ToList().Exists(x => x.Contains(ISINClaim) && x.Contains(expected)))
                claims.Add(new Claim(ISINClaim, expected));
            if (claimsForAccount.ToList().Exists(x => x.Contains(CUSIPClaim) && x.Contains(expected)))
                claims.Add(new Claim(CUSIPClaim, expected));

            var ci = new ClaimsIdentity(claims, "claims");
            var result = new ClaimsPrincipal(ci);
            return result;
        }

    }

}
